package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.ZqzrshDao;

import net.sf.json.JSONObject;
@Repository
public class ZqzrshDaoImpl extends P2PJdbcDao implements ZqzrshDao {
	
	Logger logger=Logger.getLogger(ZqzrsqDaoImpl.class);

	@Override
	public String insertZqzrsh(JSONObject params) {
		String zrr=params.optString("zrr");
		String zrje=params.optString( "zrje");
		String zrsxf=params.optString( "zrsxf");
		String zrsqrq=params.optString("zrsqrq");
		String tzid=params.optString("tzid");
		String uuid=this.getUUId();
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_tzrxx_zqzrlb(ID, ZR_USER_ID, ZR_JE, ZR_SYF, ZR_SQ_DATE, ZR_REF_TZRXX_ID, ZR_STATE) ");
		sql.append("values(:uuid,:zrr,:zrje,:zrsxf,:zrsqrq,:tzid,'01')");
		Map<String, String> paramMap=new HashMap<>();
		paramMap.put("uuid", uuid);
		paramMap.put("zrr", zrr);
		paramMap.put("zrje", zrje);
		paramMap.put("zrsxf", zrsxf);
		paramMap.put("zrsqrq", zrsqrq);
		paramMap.put("tzid", tzid);
		logger.info("insertZqzrsh sql is "+sql.toString());
		logger.info("insertZqzrsh paramMap is "+paramMap);
		int isupdateSuc=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (isupdateSuc>0) {
			return uuid;
		} else {
			return null;
		}
	}

	@Override
	public List<Map<String, Object>> queryZqzrlbList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String zrr = params.optString("zrr");
		String zrzt = params.optString("zrzt");
		String htbh = params.optString("htbh");
		String srr = params.optString("srr");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("select T1.ID,T1.ZR_USER_ID,T1.ZR_JE,T1.ZR_SYF,date_format(T1.ZR_SQ_DATE,'%Y-%m-%d') ZR_SQ_DATE");
		sql.append(",date_format(T1.ZR_START_DATE,'%Y-%m-%d') ZR_START_DATE,date_format(T1.ZR_END_DATE,'%Y-%m-%d') ZR_END_DATE");
		sql.append(",T1.ZR_STATE,T1.ZR_REF_TZRXX_ID,T1.SY_USER_ID,T1.SY_DATE,T1.SY_SYJE,T2.ZQID,T2.TCJE,date_format(T2.TZSJ,'%Y-%m-%d') TZSJ");
		sql.append(",T2.TCZT,T2.HKZT,date_format(T2.JXRQ,'%Y-%m-%d') JXRQ,T2.TZSY,T2.TZ_SOURCE,T2.POS_CODE,T2.HTBH from t_zhfw_tzrxx_zqzrlb t1");
		sql.append(" left join t_zhfw_tzrxx t2 on t1.ZR_REF_TZRXX_ID=t2.ID WHERE 1=1");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(zrr)){
			sql.append(" AND t1.ZR_USER_ID like "+"'%"+zrr+"%'");
		}
		if(StringUtils.isNotEmpty(srr)){
			sql.append(" AND t1.SY_USER_ID like "+"'%"+srr+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND t2.HTBH like "+"'%"+htbh+"%'");
		}
		if(StringUtils.isNotEmpty(zrzt)){
			sql.append(" AND t1.ZR_STATE=:zrzt");
			paramMap.put("zrzt", zrzt);
		}
		sql.append(" order by t1.ZR_SQ_DATE desc limit :start,:limit ");
		logger.info("queryZqzrlbList sql is "+sql.toString());
		logger.info("queryZqzrlbList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getZqzrlbCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String zrr = params.optString("zrr");
		String zrzt = params.optString("zrzt");
		String htbh = params.optString("htbh");
		String srr = params.optString("srr");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) from t_zhfw_tzrxx_zqzrlb t1");
		sql.append(" left join t_zhfw_tzrxx t2 on t1.ZR_REF_TZRXX_ID=t2.ID WHERE 1=1");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(zrr)){
			sql.append(" AND t1.ZR_USER_ID like "+"'%"+zrr+"%'");
		}
		if(StringUtils.isNotEmpty(srr)){
			sql.append(" AND t1.SY_USER_ID like "+"'%"+srr+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND t2.HTBH like "+"'%"+htbh+"%'");
		}
		if(StringUtils.isNotEmpty(zrzt)){
			sql.append(" AND t1.ZR_STATE=:zrzt");
			paramMap.put("zrzt", zrzt);
		}
		logger.info("getZqzrlbCount sql is "+sql.toString());
		logger.info("getZqzrlbCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public float getZqzrlbSumje(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String zrr = params.optString("zrr");
		String zrzt = params.optString("zrzt");
		String htbh = params.optString("htbh");
		String srr = params.optString("srr");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("select sum(t1.ZR_JE) ZRZJE from t_zhfw_tzrxx_zqzrlb t1");
		sql.append(" left join t_zhfw_tzrxx t2 on t1.ZR_REF_TZRXX_ID=t2.ID WHERE 1=1");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(zrr)){
			sql.append(" AND t1.ZR_USER_ID like "+"'%"+zrr+"%'");
		}
		if(StringUtils.isNotEmpty(srr)){
			sql.append(" AND t1.SY_USER_ID like "+"'%"+srr+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND t2.HTBH like "+"'%"+htbh+"%'");
		}
		if(StringUtils.isNotEmpty(zrzt)){
			sql.append(" AND t1.ZR_STATE=:zrzt");
			paramMap.put("zrzt", zrzt);
		}
		logger.info("getZqzrlbSumje sql is "+sql.toString());
		logger.info("getZqzrlbSumje paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor() {

			@Override
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				float sJe=0;
				if(rs.next()){
					sJe = rs.getFloat("ZRZJE");
				}
				return sJe;
			}
		});
	}


}
